﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_seal_save')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_seal_save

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-25 22:29:02
-- Database:    [scm_main-hysj2]
-- Description: 考勤管理-考勤封账
-- =============================================

CREATE PROCEDURE bd_proc_seal_save
  @sealYear INT
 ,@sealMonth INT
 ,@userIds VARCHAR(MAX)
 ,@sealType INT
 ,@operateId BIGINT
 ,@msg  VARCHAR(MAX) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #msg
  (
    user_serial BIGINT,
    user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
  )

  DECLARE @userList TABLE
  (
    user_serial BIGINT
  )

  IF ISNULL(@userIds, '') = '' 
  BEGIN  

    INSERT INTO #msg (user_serial, user_no, user_name, dept_name, error_msg)
    VALUES (0, '', '', '', '必须指定要封账的员工');
  	
  END 
  ELSE  
  BEGIN
    INSERT INTO @userList (user_serial)
  	SELECT value FROM STRING_SPLIT(@userIds, ',')
  END  
  


  CREATE TABLE #bill(
    user_serial BIGINT,
    bill_no VARCHAR(20),
    bill_date DATE,
    bill_status INT,
    bill_type INT   --1：加班  2：请假  3：补出勤
  )

  --@sealType   1:考勤员封账   2：HR封账    3：HR解封



  IF ISNULL(@sealType, 0) = 1
  BEGIN
    --考勤员封账验证是否还有未审批结束的申请单，申请单包括请假、加班、补出勤

    INSERT INTO #bill(user_serial, bill_no, bill_date, bill_status, bill_type)
    SELECT x.user_serial, x.order_no, x.ot_date, x.status, 1
    FROM (
      SELECT
        u.user_serial, ot_date, dbo.bd_fn_getSettleDate(ot_date) settle_ym, status, order_no
      FROM @userList u 
      INNER JOIN bd_view_overtime ot ON ot.user_serial = u.user_serial
      WHERE status < 3
    ) x
    WHERE YEAR(x.settle_ym) = @sealYear AND MONTH(x.settle_ym) = @sealMonth

    INSERT INTO #bill(user_serial, bill_no, bill_date, bill_status, bill_type)
    SELECT x.user_serial, x.order_no, x.kssj, x.status, 2
    FROM (
      SELECT
        u.user_serial, kssj, dbo.bd_fn_getSettleDate(kssj) settle_ym, status, order_no
      FROM @userList u 
      INNER JOIN bd_view_leave lv ON lv.user_serial = u.user_serial
      WHERE status < 3
    ) x
    WHERE YEAR(x.settle_ym) = @sealYear AND MONTH(x.settle_ym) = @sealMonth


    INSERT INTO #bill(user_serial, bill_no, bill_date, bill_status, bill_type)
    SELECT x.user_serial, x.order_no, x.sj, x.status, 3
    FROM (
      SELECT
        u.user_serial, CONVERT(VARCHAR(10), sj, 120) sj, dbo.bd_fn_getSettleDate(sj) settle_ym, status, order_no
      FROM @userList u
      INNER JOIN bd_view_repair repair ON repair.user_serial = u.user_serial
      WHERE status < 3
    ) x
    WHERE YEAR(x.settle_ym) = @sealYear AND MONTH(x.settle_ym) = @sealMonth

    INSERT INTO #msg(user_serial, error_msg)
    SELECT DISTINCT user_serial, 
       CASE bill_type
       WHEN 1 THEN
         '加班单审批未结束, 单号: ' + bill_no --+ ', 加班日期:' + CONVERT(VARCHAR(10), bill_date, 120)
       WHEN 2 THEN
         '请假单审批未结束, 单号: ' + bill_no --+ ', 请假日期:' + CONVERT(VARCHAR(10), bill_date, 120)
       WHEN 3 THEN
         '补出勤单审批未结束, 单号: ' + bill_no --+ ', 补出勤日期:' + CONVERT(VARCHAR(10), bill_date, 120)
       ELSE
         ''
     END
    FROM (SELECT DISTINCT user_serial, bill_no, bill_type FROM #bill) bill


    --已封账员工
    INSERT INTO #msg(user_serial, error_msg)
    SELECT user_serial, 
     CASE
       WHEN lock_status = 1 THEN
         '考勤员已封账'
       WHEN lock_status = 2 THEN
         'HR已封账'
       ELSE
         ''
     END
    FROM bd_kq_accounts
    WHERE [year] = @sealYear AND [month] = @sealMonth AND lock_status > 0 AND user_serial IN (
        SELECT
          user_serial
        FROM @userList
      )

  END

    --HR封账
  IF ISNULL(@sealType, 0) = 2
  BEGIN

    --检测考勤员封账
    INSERT INTO #msg (user_serial, error_msg)
    SELECT user_serial, '考勤员未封账'
    FROM bd_kq_accounts
    WHERE [year] = @sealYear AND [month] = @sealMonth AND lock_status = 0 AND  user_serial IN (
        SELECT
          user_serial
        FROM @userList
      )

    --HR不能重复封账
    INSERT INTO #msg (user_serial, error_msg)
    SELECT user_serial, 'HR已经封账，不能重复封账'
    FROM bd_kq_accounts
    WHERE [year] = @sealYear AND [month] = @sealMonth AND lock_status = 2 AND  user_serial IN (
        SELECT
          user_serial
        FROM @userList
      )

    --检测是否做加班结算
    INSERT INTO #msg (user_serial, error_msg)
    SELECT user_serial, '未做加班结算, 不能封账'
    FROM @userList
    WHERE user_serial NOT IN (
        SELECT
          user_serial
        FROM bd_kq_ot_settle
        WHERE settle_year = @sealYear AND settle_month = @sealMonth
      )
  END

  --删除有问题的数据
  DELETE FROM @userList
  WHERE user_serial IN (
      SELECT DISTINCT
        user_serial
      FROM #msg
    )


  

  DECLARE @isOK INT = 0
  IF EXISTS(
  	SELECT 1
  	FROM #msg
  ) 
  BEGIN
    DELETE FROM @userList WHERE user_serial IN (SELECT DISTINCT user_serial FROM #msg)

    SET @isOK = 0

    UPDATE msg
    SET user_no = u.user_no
       ,user_name = u.user_lname
       ,dept_name = u.dep_name
    FROM #msg msg
    INNER JOIN (
        SELECT
          user_serial, user_no, user_lname, dep_name
        FROM bd_view_user_all
      ) u ON u.user_serial = msg.user_serial

    SET @msg = (SELECT * FROM #msg FOR JSON AUTO)
  END
  
  
  BEGIN TRY
  	BEGIN TRANSACTION

      DECLARE @userJson VARCHAR(MAX) = ''
      SET @userJson = (SELECT DISTINCT user_serial FROM @userList FOR JSON AUTO)

      IF @sealType IN(1, 2) 
      BEGIN  
        --封账
        UPDATE bd_kq_accounts
        SET lock_status =
          CASE
            WHEN @sealType = 1 THEN
              1
            WHEN @sealType = 2 THEN
              2
            ELSE
              lock_status
          END
        WHERE [year] = @sealYear AND [month] = @sealMonth AND user_serial IN (
          SELECT
            user_serial
          FROM @userList
        )  	
    
        IF @sealType = 2 
        BEGIN  
          --HR封账，处理调休假期数据
          EXEC bd_proc_seal_calc_dayoff @sealYear, @sealMonth, @userJson, 1, @operateId        	
        END
      END
      ELSE IF @sealType = 3 
      BEGIN  
        --取消封账
        UPDATE bd_kq_accounts
        SET lock_status = IIF(lock_status - 1 <= 0, 0, lock_status - 1)
        WHERE [year] = @sealYear AND [month] = @sealMonth AND user_serial IN (
          SELECT
            user_serial
          FROM @userList
        )  
          
        IF @sealType = 3 
        BEGIN  
          --HR取消封账，处理调休假期数据
          EXEC bd_proc_seal_calc_dayoff @sealYear, @sealMonth, @userJson, 2, @operateId        	
        END	
      END  		
  	COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
    END;
    RAISERROR (@errStr, 16, 1);
  END CATCH;


  --返回消息 
  DECLARE @temp TABLE
  (
  	success INT,
    msg VARCHAR(MAX)
  )

  INSERT INTO  @temp (success, msg)
  VALUES (@isOK, @msg);
  
  SET @msg = (SELECT * FROM @temp FOR JSON AUTO)

  SELECT @msg

  DROP TABLE #bill
  DROP TABLE #msg

  SET NOCOUNT OFF;
END